---
title: 'Database schema'
description: 'Learn how to extend and modify the database schema'
---

<Info>
  It is recommended to [learn more about
  Drizzle](https://orm.drizzle.team/learn) before diving into these guides.
</Info>

## Modifing the schema

The database schema is managed by Drizzle ORM in lives in `packages/db/src/db.ts`.
Tables are defined in separate schema files, `packages/db/src/[table]/[table].sql.ts`.

### Adding a new table

Let's add a new database table for a simple todo list.

Go ahead a create a new file in `packages/db/src/todos/todos.sql.ts` with the following content.

```ts
import { varchar } from 'drizzle-orm/pg-core'

import { pgTable } from '../_table'
import { timestamps, workspaceId, userId } from '../utils'

export const todos = pgTable('todos', {
  ...workspaceId,
  userId: userId('user_id'),
  todo: varchar('avatar', { length: 255 }),
  ...timestamps,
})
```

Let's dissect what's going on here.

We imported `pgTable` from `../_table`, this is a custom table creator that allows you to prefix your tables in order to use the same database for multiple projects.

The `workspaceId,` is a utility that adds a primary key and workspaceId to the table. `userId('user_id')` is utility to create columns that contain user ids.

Lastly `timestamps` will add a `created_at` and `updated_at` column to our table.

To use our new todos table with Drizzle ORM we need to import it and add it to our schema.

Open `packages/db/src/db.ts`, import `todos.sql.ts` and add it to the `schema` object.

```ts
import * as todos from './todos/todos.sql'

const schema = {
  // other tables...
  todos,
}
```

That's it, now we can update the database and query the new table.

### Migrating changes

For testing purposes you can run `yarn db:push` to push the changes to your local database.

Use `yarn db:migrate` to create a new migration.

[Learn more about migrations](/docs/nextjs-starter-kit/database/migrations).

## Conventions

- Schema file extensions are prefixed with `.sql`, eg `todos.sql.ts`.
  Drizzle Kit will automatically stitch these files together in a single schema.

- ID's use [Cuid2](https://github.com/paralleldrive/cuid2). Cuid2 ID's are secure, collision-resistant ids optimized for horizontal scaling and performance.

- Table names are plural, eg `todos`, `workspaces`, etc.

## Utilities

The database package ships with a set of reusable utilities to help you build consistent schemas.

| Name        | Description                                                 | Usage                   |
| ----------- | ----------------------------------------------------------- | ----------------------- |
| createId    | Create a new CUID2 id                                       | `createId()`            |
| workspaceId | Define the primary key and workspaceId on a table           | `...workspaceId`        |
| id          | Define the primary key will create a new CUID2 on insert    | `...id`                 |
| timestamps  | Defines created_at and updated_at columns using timestampz  | `...timestamps`         |
| userId      | Returns column definition for columns that contain a userId | `userId('column_name')` |
